package com.geekstudy.week7;

import com.geekstudy.week7.entity.ShopOrder;

import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.ThreadLocalRandom;

public class Week7Work2 {

    static final int ORDER_ID_BASE = 100000000;

    public static void truncateTable(Statement statement) throws SQLException{
        boolean truncateResult = statement.execute("truncate table shop_order");
        System.out.println("\t truncate shop_order result:" + truncateResult);
    }

    public static ShopOrder createShopOrder(int size) {
        ShopOrder shopOrder = new ShopOrder();
        shopOrder.setOrderId(String.valueOf(ORDER_ID_BASE + size));
        int userId = ThreadLocalRandom.current().nextInt(100000, 999999);
        shopOrder.setOrderUserId(String.valueOf(userId));
        int price = ThreadLocalRandom.current().nextInt(1000);
        shopOrder.setOrderFullPrice(new BigDecimal(price));
        shopOrder.setOrderDiscountPrice(new BigDecimal(price));
        Date currentDate = new Date();
        shopOrder.setCreateTime(currentDate);
        shopOrder.setUpdateTime(currentDate);
        return shopOrder;
    }

    public static void normalInsert(Statement statement, int totalSize) throws SQLException {
        truncateTable(statement);
        SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        long startMillis = System.currentTimeMillis();
        for(int size = 1; size <= totalSize; size++) {
            ShopOrder shopOrder = createShopOrder(size);


            String insertSql = "insert into shop_order "
                    + "(order_id, order_user_id, order_full_price, order_discount_price,create_time, update_time) "
                    + " values ('" + shopOrder.getOrderId() + "', '" + shopOrder.getOrderUserId()
                    + "', '" + shopOrder.getOrderFullPrice() + "', '" + shopOrder.getOrderDiscountPrice()
                    + "', '" + simpleFormat.format(shopOrder.getCreateTime()) + "', '" + simpleFormat.format(shopOrder.getUpdateTime()) + "') ";

            statement.execute(insertSql);
        }
        long finishMillis = System.currentTimeMillis();
        long usedMillis = finishMillis - startMillis;
        long usedSecond = usedMillis / 1000;
        long usedMinute = usedMillis / (1000 * 60);
        System.out.println("\t normal totalSize:" + totalSize + " usedMillis:" + usedMillis + " usedSecond:" + usedSecond + " usedMinute:" + usedMinute);
    }

    public static void batchInsert(Statement statement, PreparedStatement preparedStatement, int totalSize) throws SQLException {
        truncateTable(statement);

        SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        long startMillis = System.currentTimeMillis();

        for(int size = 1; size <= totalSize; size++) {
            ShopOrder shopOrder = createShopOrder(size);

            preparedStatement.setString(1, shopOrder.getOrderId());
            preparedStatement.setString(2, shopOrder.getOrderUserId());
            preparedStatement.setString(3, String.valueOf(shopOrder.getOrderFullPrice()));
            preparedStatement.setString(4, String.valueOf(shopOrder.getOrderDiscountPrice()));
            preparedStatement.setString(5, simpleFormat.format(shopOrder.getCreateTime()));
            preparedStatement.setString(6, simpleFormat.format(shopOrder.getUpdateTime()));
            preparedStatement.addBatch();

            if(size % 10000 == 0) {
                preparedStatement.executeBatch();
            }
        }
        long finishMillis = System.currentTimeMillis();
        long usedMillis = finishMillis - startMillis;
        long usedSecond = usedMillis / 1000;
        long usedMinute = usedMillis / (1000 * 60);
        System.out.println("\t PreparedStatement batch totalSize:" + totalSize + " usedMillis:" + usedMillis + " usedSecond:" + usedSecond + " usedMinute:" + usedMinute);

    }

    public static void main(String[] args) {

        try {
            String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/geek_java4";
            String username = "java4";
            String password = "java4";

            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            Statement statement = connection.createStatement();


            int totalSize = 10000 * 1000;
            normalInsert(statement, totalSize);

            String batchSql = "insert into shop_order(order_id, order_user_id, order_full_price, order_discount_price, create_time, update_time ) "
                    + "   values (?, ?, ?, ?, ?, ?) ";
            PreparedStatement preparedStatement = connection.prepareStatement(batchSql);
            batchInsert(statement, preparedStatement, totalSize);

        } catch(Exception e) {
            e.printStackTrace();
        }


    }
}
